/**
 * 
 */
package cz.geoserver.KMLpublisher.util;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

import cz.geoserver.KMLpublisher.db.model.BBOX;
import cz.geoserver.KMLpublisher.db.model.ClusterInfo;
import cz.geoserver.KMLpublisher.db.model.TableName;

/**
 * Class parses incoming SQL query. Checks whether it is SELECT query and whether has mandatory parameter asKML.
 * Table name and name of geometry column is used for hierarchical mechanism. 
 * @author mkepka
 *
 */
public class SQLparser {

    private String sqlOriginal;     // original SQL command sent by client
    private String columnsString;   // String with selected columns from original SQL 
    private String tablesString;    // String with table names from original SQL
    private String whereClause;     // String with WHERE clause from original SQL 
    private String sortString;      // String with sorting parameters like GROUP BY, ORDER BY etc. from original SQL
    
    private String geomColumn;          // name of geometry column in data table
    private List<String> columnsList;   // List of selected columns names
    private List<TableName> tableList;  // List of selected table names
    
    private ClusterInfo clusterInfo;    // Object with additional info about hierarchy
    private boolean isSetClusterInfo;   // says if is set cluster info
    
    private String sqlWithSpatialQuery;              // modified SQL command with added spatial query
    private String sqlWithSpatialQueryAndHierarchy;  // modified SQL command with added spatial query and selecting level of hierarchy
    
    // Forms of SQL keywords
    private String[] selectForms = {"SELECT", "select", "Select"};
    private String[] fromForms = {" FROM ", " from ", " From "};
    private String[] whereForms = {" WHERE ", " where ", " Where "};
    private String[] limitForms = {" LIMIT ", " limit ", " Limit "};
    private String[] orderForms = {" ORDER BY ", " order by ", " Order by ", " Order By ", " order By "};
    private String[] groupForms = {" GROUP BY ", " group by ", " Group by ", " Group By ", " group By "};
    private String[] havingForms = {"HAVING", "having", "Having"};
    private String[] asKMLForms = {"ST_AsKML(", "ST_ASKML(", "st_asKML(", "st_askml(", "ASKML(", "askml(", "asKML("};

    /**
     * Constructor creates instance of class and parses incoming SQL query.
     * This constructor does not use hierarchical mechanism
     * @param sql - String with incoming SQL query
     * @throws SQLException - Throws SQLException if SQL query is not SELECT query or miss any mandatory KEY word
     */
    public SQLparser(String sql) throws SQLException{
        parseSQL(sql);
    }
    
    /**
     * Constructor creates instance of class and parses incoming SQL query.
     * This constructor uses hierarchical mechanism
     * @param sql - String with incoming SQL query
     * @param cluster - Instance of ClusterInfo class that holds information for hierarchical 
     * mechanism for current combination of data table and clustered attribute in incoming SQL
     * @throws SQLException - Throws SQLException if SQL query is not SELECT query or miss any mandatory KEY word
     */
    public SQLparser(String sql, ClusterInfo cluster) throws SQLException{
        this.clusterInfo = cluster;
        this.isSetClusterInfo = true;
        parseSQL(sql);
    }
    
    /**
     * Method returns original incoming SQL query 
     * @return the sqlString - String with unparsed SQL query
     */
    public String getSqlOriginalString() {
        return sqlOriginal;
    }

    /**
     * Method returns part of SQL query between keywords SELECT and FROM as String 
     * @return the columnsString - part of SQL between keywords SELECT and FROM
     */
    public String getColumnsString() {
        return columnsString;
    }

    /**
     * Method returns part of SQL query between keywords FROM and WHERE or sorting keywords as String 
     * @return the tablesString - part of SQL query between keywords FROM and WHERE or sorting keywords
     */
    public String getTablesString() {
        return tablesString;
    }

    /**
     * Method returns part of SQL query after keyword WHERE includes sorting keywords as String 
     * @return the whereClause - part of SQL query after keyword WHERE includes sorting keywords 
     * or empty String if there is not any WHERE keyword in SQL
     */
    public String getWhereClause() {
        return whereClause;
    }

    /**
     * Method returns list of TableName classes that contain names of tables in SQL query
     * @return - list of TableName classes that contain names of tables in SQL query
     */
    public List<TableName> getTableList(){
        return tableList;
    }

    /**
     * Method returns part of SQL query with sorting keywords ORDER BY, GROUP BY, HAVING 
     * if there are any in SQL 
     * @return part of SQL query with sorting keywords ORDER BY, GROUP BY, HAVING
     * or empty String if there are not any sorting keywords in SQL
     */
    public String getSortString(){
        return sortString;
    }

    /**
     * Method returns name of geometry column that contains data that will be published in KML.
     * Name of geometry column came from SQL mandatory parameter AsKML()
     * @return the geomColumn - Name of geometry column as String
     */
    public String getGeomColumn() {
        return geomColumn;
    }

    /**
     * Method returns list of selected columns in SQL as Strings, all parameters between keywords SELECT and FROM
     * @return the columnsList - List of selected columns in SQL
     */
    public List<String> getColumnsList() {
        return columnsList;
    }

    /**
     * Method returns spatial query for selecting geometry features that are located in BBOX polygon
     * @param bbox - Instance of BBOX class that contain corners of current extent
     * @return - spatial query for selecting geometry features as String 
     * Form of query: If A's bounding box overlaps B's bounding box the operator returns true.
     */
    public String getBboxPolygonClause(BBOX bbox) {
        String spatQuery = geomColumn+" && transform(polygonFromText('"+bbox.getBboxPolygon()+"', 4326), st_srid("+geomColumn+"))";
        return spatQuery;
    }

    /**
     * Method modifies original SQL query by adding spatial query on geometries in current BBOX
     * @param bbox - Instance of BBOX class that contain corners of current extent
     * @return - modified SQL with added spatial query as String
     */
    public String getSqlWbbox(BBOX bbox) {
        String bboxClause = getBboxPolygonClause(bbox);
        String sqlModif = "SELECT "+columnsString+" FROM "+tablesString+" WHERE "+bboxClause;
        if(whereClause == null || whereClause.isEmpty()){
            sqlModif = sqlModif + "";
        }
        else{
            sqlModif = sqlModif + " AND " + whereClause;
        }
        if(sortString == null || sortString.isEmpty()){
            sqlModif = sqlModif + ";";
        }
        else{
            sqlModif = sqlModif + " " + sortString + ";";
        }
        this.sqlWithSpatialQuery = sqlModif;
        return sqlModif;
    }

    /**
     * Method modifies original SQL query by adding spatial query on geometries in current BBOX 
     * and with selecting corresponding level of hierarchy
     * @param level - corresponding level of hierarchy as int
     * @param bbox - Instance of BBOX class that contain corners of current extent
     * @return - modified SQL with added spatial query and corresponding level of hierarchy as String
     * or empty String if was not set ClusterInfo before
     */
    public String getSqlWhierarchyAbbox(int level, BBOX bbox) {
        if(isSetClusterInfo){
            String bboxClause = getBboxPolygonClause(bbox);
            String clusterQuery = clusterInfo.getDataIdColumnName()+"="+clusterInfo.getClusterFeatureIdName()+" AND "+clusterInfo.getClusterLevelName()+"="+level;
            String sqlModif = "SELECT "+columnsString+" FROM "+tablesString+", "+clusterInfo.getClustersTableName()+" WHERE "+bboxClause+" AND "+clusterQuery;
            if(whereClause == null || whereClause.isEmpty()){
                sqlModif = sqlModif + " ";
            }
            else{
                sqlModif = sqlModif + " AND " + whereClause;
            }
            if(sortString == null || sortString.isEmpty()){
                sqlModif = sqlModif + ";";
            }
            else{
                sqlModif = sqlModif + " " + sortString + ";";
            }
            this.sqlWithSpatialQueryAndHierarchy=sqlModif;
            return sqlModif;
        }
        else{
            return "";
        }
    }

    /**
     * Method returns information whether ClusterInfo was set. 
     * @return true if was set, false if not
     */
    public boolean isSetClusterInfo(){
        return isSetClusterInfo;
    }

    /**
     * Method returns instance of ClusterInfo that belongs to incoming SQL 
     * @return the clusterInfo - instance of ClusterInfo that belongs to incoming SQL
     */
    public ClusterInfo getClusterInfo() {
        return clusterInfo;
    }

    /**
     * Method returns modified SQL with spatial query in Where clause that was use for loading data 
     * @return the sqlWithSpatialQuery - String with modified SQL with spatial query in Where clause 
     * or null if has been not called method getSqlWbbox() yet 
     */
    public String getSqlWithSpatialQuery() {
        return sqlWithSpatialQuery;
    }

    /**
     * Method returns modified SQL with spatial query and level definition in Where clause that was use for loading data
     * @return the sqlWithSpatialQueryAndHierarchy - String with modified SQL with spatial query in Where clause
     * or null if has been not called method getSqlWhierarchyAbbox() yet
     */
    public String getSqlWithSpatialQueryAndHierarchy() {
        return sqlWithSpatialQueryAndHierarchy;
    }

    /**
     * Method parses incoming SQL according to keywords. It splits query into several parts that are used in next methods. 
     * @param sql - Original incoming SQL as String
     * @throws SQLException - Throws SQLException if SQL is not SELECT query or does not contain mandatory parameters
     */
    private void parseSQL(String sql) throws SQLException{
        String select = findParameter(selectForms, sql);
        
        // if there is SELECT keyword at beginning of query
        if(!select.isEmpty() && sql.startsWith(select)){
            this.sqlOriginal=sql;
            String from = findParameter(fromForms, sql);
            
            // if there if FROM keyword in SQL
            if(!from.isEmpty()){
                int selectEnd = sql.indexOf(select)+6; // 6 chars
                int fromStart = sql.indexOf(from);
                int fromEnd = sql.indexOf(from)+6;     // 4 chars and 2 spaces !!
                this.columnsString = sql.substring(selectEnd, fromStart).trim();
                
                // processing part of SQL after table names
                String where = findParameter(whereForms, sql);
                int semicolonIndex = sql.indexOf(";");
                
                List<String[]> sortParamFormsList = new ArrayList<String[]>();
                sortParamFormsList.add(0, groupForms);
                sortParamFormsList.add(1, orderForms);
                sortParamFormsList.add(2, limitForms);
                sortParamFormsList.add(3, havingForms);
                
                // try to find any sorting keywords in SQL
                Map<Integer, String> sortParamsMap = findFormerParameter(sql, sortParamFormsList);
                
                // if there is WHERE keyword in SQL
                if(!where.isEmpty()){
                    int whereStart = sql.indexOf(where);
                    int whereEnd = sql.indexOf(where)+7; // 5 chars and 2 spaces !!
                    this.tablesString = sql.substring(fromEnd, whereStart).trim();
                    
                    // if there are not any sorting keywords in SQL 
                    if(sortParamsMap.isEmpty()){
                        this.whereClause = sql.substring(whereEnd, semicolonIndex).trim();
                    }
                    else{
                        int firstIndex = sortParamsMap.keySet().iterator().next().intValue();
                        this.whereClause = sql.substring(whereEnd, firstIndex).trim();
                        this.sortString = sql.substring(firstIndex, semicolonIndex).trim();
                    }
                }
                // if there is not WHERE keyword in SQL
                else{
                    if(sortParamsMap.isEmpty()){
                        this.tablesString = sql.substring(fromEnd, semicolonIndex).trim();
                    }
                    else{
                        int firstIndex = sortParamsMap.keySet().iterator().next().intValue();
                        this.tablesString = sql.substring(fromEnd, firstIndex).trim();
                        this.sortString = sql.substring(firstIndex, semicolonIndex).trim();
                    }
                }
                // creating list of table names and column names
                this.tableList = getTableNameList(tablesString);
                this.columnsList = getColumnNameList(columnsString);
            }
            // if there is not FROM keyword in SQL 
            else{
                throw new SQLException("SQL SELECT does not contain mandatory FROM parameter!");
            }
        }
        //if incoming SQL is not SELECT query
        else{
            throw new SQLException("SQL command does not contain mandatory SELECT parameter!");
        }
    }

    /**
     * Method try to find some form of parameter in SQL String 
     * @param forms - String array with possible forms of wanted parameter 
     * @param sqlPart - String in that should be parameter
     * @return String - form of parameter that is in SQL, or empty String if there is not any.
     */
	private String findParameter(String[] forms, String sqlPart){
        String param = "";
        for(int i=0; i<forms.length; i++){
            boolean contain = sqlPart.contains(forms[i]);
            if(contain){
                param = forms[i];
                break;
            }
        }
        return param;
    }

    /**
     * Method try to find which possible form of parameter is first used in SQL.
     * Method is used to find which sorting keyword starts sorting part of SQL.
     * @param query - String where forms will be searched.
     * @param formsList - List of String arrays with possible forms of wanted parameter
     * @return - TreeMap<Integer, String> with sorted parameters, 
     * Integer is index of parameter in SQL and String is current form of parameter in SQL.
     * Map is empty if there are not any forms of parameters 
     */
    private Map<Integer, String> findFormerParameter(String query, List<String[]> formsList){
        Map<Integer, String> map = new TreeMap<Integer, String>();
        for(int i = 0; i <formsList.size();i++){
            String[] forms = formsList.get(i);
            String param = findParameter(forms, query);
            if(!param.isEmpty()){
                int index = query.indexOf(param);
                map.put(Integer.valueOf(index), param);
            }
        }
        return map;
    }

    /**
     * Method creates list of TableName classes from String of tables that are after FROM keyword in SQL.
     * Method supposes that tables are separated by comma and table names and aliases by space  
     * @param tablesString - String of table names part of SQL
     * @return LinkedList of TableName classes that are in SQL select part
     */
    private List<TableName> getTableNameList(String tablesString){
        List<TableName> tableList = new LinkedList<TableName>();
        boolean isComma = tablesString.contains(",");
        // if there is some comma in String => must be there more than 1 table name
        if(isComma){
            String[] tables = tablesString.split(",");
            for(int i = 0; i<tables.length; i++){
                String table = tables[i].trim();
                boolean isSpace = table.contains(" ");
                // if there is some space in this part => must be there some alias
                if(isSpace){
                    String[] tabAndAlias = table.split(" ");
                    TableName tabAndAl = new TableName(tabAndAlias[0].trim(), tabAndAlias[1].trim());
                    tableList.add(tabAndAl);
                }
                //if there is not any space in this part => there is not any alias
                else{
                    TableName tab = new TableName(table);
                    tableList.add(tab);
                }
            }
        }
        // if there is not any comma => there is only 1 table name
        else{
            boolean isSpace = tablesString.contains(" ");
         // if there is some space in this part => must be there some alias
            if(isSpace){
                String[] tabAndAlias = tablesString.split(" ");
                TableName tabAndAl = new TableName(tabAndAlias[0].trim(), tabAndAlias[1].trim());
                tableList.add(tabAndAl);
            }
          // if there is not any space in this part => there is not any alias
            else{
                TableName tab = new TableName(tablesString);
                tableList.add(tab);
            }
        }
        return tableList;
    }

    /**
     * Method finds geometry column name in AsKML function 
     * Method supposes general form of function: ST_AsKML([version], geometry, [precision])
     * @param kmlParName - String with AsKML function
     * @param columnString - name of geometry column as String
     */
    private void findGeomColumn(String kmlParName, String columnString){
        String geomColCand = columnString.replace(kmlParName, "(");
        String kmlParam = geomColCand.substring(geomColCand.indexOf("(")+1, geomColCand.indexOf(")"));
        String[] kmlParamSplit = kmlParam.split(",");
        int sizeParams = kmlParamSplit.length;
        // if there is only 1 part => only geometry column name
        if(sizeParams==1){
            this.geomColumn=kmlParamSplit[0].trim();
        }
        // if there are 3 parts => geometry column name is in the middle
        else if(sizeParams==3){
            this.geomColumn=kmlParamSplit[1].trim();
        }
        // if there is 2 parts => column name can be first before precision or second after version 
        else{
            // try to parse first part that can be version
            try{
                int version = Integer.parseInt(kmlParamSplit[0].trim());
                this.geomColumn=kmlParamSplit[1].trim();
            }
            // if first part cannot be parsed, it is geometry column
            catch(NumberFormatException e){
                this.geomColumn=kmlParamSplit[0].trim();
            }
        }
    }

    /**
     * Method creates list of column names from String of columns that are in SQL after SELECT keyword.
     * Method supposes that column names are separated by commas
     * @param columnsString - String with column names from SQL
     * @return - LinkedList with names of columns as Strings
     * @throws SQLException - throws SQLException if there is not found mandatory AsKML parameter in column list 
     */
    private List<String> getColumnNameList(String columnsString) throws SQLException{
        List<String> colList = new LinkedList<String>();
        String paramKML = findParameter(asKMLForms, columnsString);
        // if there is AsKML(...) parameter in String
        if(!paramKML.isEmpty()){
            int kmlIndex = columnsString.indexOf(paramKML);
            int parenthesisIndex = columnsString.indexOf(")", kmlIndex);
            
            // if asKML is not first column name in String, String must be divided into 3 parts
            if(kmlIndex != 0){
                // all column names before AsKML(...) parameter
                String firstPart = columnsString.substring(0, kmlIndex);
                String[] firstParts = firstPart.split(",");
                for(int i = 0; i<firstParts.length; i++){
                    String part = firstParts[i].trim();
                    if(!part.isEmpty()){
                        colList.add(part);
                    }
                }
            }
            
            // middle part with whole AsKML(...) parameter 
            String kmlPart = columnsString.substring(kmlIndex, parenthesisIndex+1).trim();
            colList.add(kmlPart);
            findGeomColumn(paramKML, kmlPart);
            
            // if after AsKML(...) parameter are any other column names
            if(parenthesisIndex != columnsString.length()){
                String lastPart = columnsString.substring(parenthesisIndex+1, columnsString.length());
                String[] lastParts = lastPart.split(",");
                for(int i = 0; i<lastParts.length; i++){
                    String part = lastParts[i].trim();
                    if(!part.isEmpty()){
                        colList.add(part);
                    }
                }
            }
            return colList;
        }
        // if there is not AsKML(...) parameter
        else{
            throw new SQLException("There is not mandatory asKML parameter!");
        }
    }
}